package myJDBC.lowLevel;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.OracleTypes;
import myJDBC.myConnection;

public class ListValues {

	public static int createValue(int order, String value) throws SQLException{
		int i=-1;
		CallableStatement  cs= null;
			try {
				StringBuilder plsql = new StringBuilder();
				           plsql.append("begin ")
				       			.append("insert into \"list_values\"(\"order\",\"value\") values (?,?) ")
				       			.append("RETURNING \"list_value_id\" ")
				       			.append("INTO ? ; ")
				       			.append("END;");
				cs=myConnection.dbConnection.prepareCall(plsql.toString());
				cs.setInt(1, order);
				cs.setString(2, value);
				cs.registerOutParameter(3, OracleTypes.NUMBER);
				cs.execute();
				i=cs.getInt(3);
			} finally {
				if (cs != null)
					cs.close();
			}			
	return i;
	}
	
	public static int getValueId(int number, int attrId){
		int ret = -1;
		StringBuilder selectAttributes = new StringBuilder();
	    selectAttributes.append("select \"list_value_id\" ") 
						.append("from \"list_values\" ")
						.append("where \"list_value_id\" in( ")
						.append("select \"list_value_id\" ")
						.append("from \"attr_type_def\" ")
						.append("where \"attr_type_def_id\"=?) ")
						.append("and \"order\"=? ");
		if (myConnection.dbConnection != null) {
		    try (PreparedStatement pstmt = myConnection.dbConnection
	                .prepareStatement(selectAttributes.toString())) {
			   pstmt.setInt(1, attrId);
			   pstmt.setInt(2, number);
			   pstmt.executeUpdate(); 
			   ResultSet rs = pstmt.executeQuery();	
				while (rs.next()) {
					ret = rs.getInt(1);
				}
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
			return ret;
		}
		return ret;
	}
	
}
